﻿using STHPMS1.DAL.D_Core;
using System.Data;
using ServiceStack.OrmLite;
using STHPMS1.ICore.I_Entity.Stmpms1;
using STHPMS1.Common.C_Entity.Stmpms1;
using STHPMS1.ICore.I_DAL.Repository;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace STHPMS1.DAL.D_Repository
{
    public class Dp_Prevemeas_IniRepository : DalBase, IDp_Prevemeas_IniRepository
    {

        /// <summary>
        /// 病害表中提取所有病害
        /// </summary>
        /// <param name="segid">决策路段</param>
        /// <returns></returns>
        public DataTable GetMainSegid_ASP(string segid)
        {
//            string sql = @"select sum(ALGCK_L+ALGCK_M) as ALGCK_LM,sum(ALGCK_H) as ALGCK_H,
//            sum([BLFT_L]) as BLFT_L,SUM([BLFT_H]) as BLFT_H,sum(LGCK_L+TRSCK_L) as CRACK_L,sum(LGCK_H+TRSCK_H) as CRACK_H,
//            sum(PIT_L) as PIT_L,sum(PIT_H) as PIT_H,sum(LOS_L) as LOOSE_L,sum(LOS_H) as LOOSE_H,sum(SINK_L) as SINK_L,sum(SINK_H) as SINK_H,
//            sum(PLWV_L+PLWV_H) as [PLWV],sum(RUT_L) as RUT_L,sum(RUT_H) as RUT_H,sum([BLED]) as bled,sum([REPAIR]) as repair
//            from S_PMDS_ASP where UNIT in(select UNIT from B_INVNTORY_UNIT where SEGID='" + segid + "')";


//            string sql = @"select isnull(sum(ALGCK_L+ALGCK_M),0) as ALGCK_LM,isnull(sum(ALGCK_H),0) as ALGCK_H,
//                            isnull(sum([BLFT_L]),0) as BLFT_L,isnull(SUM([BLFT_H]),0) as BLFT_H,isnull(sum(LGCK_L+TRSCK_L),0) as CRACK_L,
//                            isnull(sum(LGCK_H+TRSCK_H),0) as CRACK_H,isnull(sum(PIT_L),0) as PIT_L,isnull(sum(PIT_H),0) as PIT_H,
//                            isnull(sum(LOS_L),0) as LOOSE_L,isnull(sum(LOS_H),0) as LOOSE_H,isnull(sum(SINK_L),0) as SINK_L,isnull(sum(SINK_H),0) as SINK_H,
//                            isnull(sum(PLWV_L+PLWV_H),0) as [PLWV],isnull(sum(RUT_L),0) as RUT_L,isnull(sum(RUT_H),0) as RUT_H,isnull(sum([BLED]),0) as bled,
//                            isnull(sum([REPAIR]),0) as repair from S_PMDS_ASP
//                            where UNIT in(select UNIT from B_INVNTORY_UNIT where SEGID='" + segid + "')";


              //加入权重和影响面积
//            string sql = @"select isnull(sum(ALGCK_L*0.6+ALGCK_M*0.8),0) as ALGCK_LM,isnull(sum(ALGCK_H*1),0) as ALGCK_H,
//                            isnull(sum([BLFT_L]*0.6),0) as BLFT_L,isnull(SUM([BLFT_H]*0.8),0) as BLFT_H,isnull(sum(LGCK_L*0.6*0.2+TRSCK_L*0.6*0.2),0) as CRACK_L,
//                            isnull(sum(LGCK_H*1*0.2+TRSCK_H*1*0.2),0) as CRACK_H,isnull(sum(PIT_L*0.8),0) as PIT_L,isnull(sum(PIT_H*1),0) as PIT_H,
//                            isnull(sum(LOS_L*0.6),0) as LOOSE_L,isnull(sum(LOS_H*1),0) as LOOSE_H,isnull(sum(SINK_L*0.6),0) as SINK_L,isnull(sum(SINK_H*1),0) as SINK_H,
//                            isnull(sum(PLWV_L*0.6+PLWV_H*1),0) as [PLWV],isnull(sum(RUT_L*0.6*0.4),0) as RUT_L,isnull(sum(RUT_H*1*0.4),0) as RUT_H,isnull(sum([BLED]*0.2),0) as bled,
//                            isnull(sum([REPAIR]*0.1),0) as repair from S_PMDS_ASP 
//                            where UNIT in(select UNIT from B_INVNTORY_UNIT where SEGID='" + segid + "')";

            string sql = @"select isnull(sum(ALGCK_L*0.6+ALGCK_M*0.8),0) as ALGCK_LM,isnull(sum(ALGCK_H*1),0) as ALGCK_H,
                            isnull(sum([BLFT_L]*0.6),0) as BLFT_L,isnull(SUM([BLFT_H]*0.8),0) as BLFT_H,isnull(sum(LGCK_L*0.6*0.2+TRSCK_L*0.6*0.2),0) as CRACK_L,
                            isnull(sum(LGCK_H*1*0.2+TRSCK_H*1*0.2),0) as CRACK_H,isnull(sum(PIT_L*0.8),0) as PIT_L,isnull(sum(PIT_H*1),0) as PIT_H,
                            isnull(sum(LOS_L*0.6),0) as LOOSE_L,isnull(sum(LOS_H*1),0) as LOOSE_H,isnull(sum(SINK_L*0.6),0) as SINK_L,isnull(sum(SINK_H*1),0) as SINK_H,
                            isnull(sum(PLWV_L*0.6+PLWV_H*1),0) as [PLWV],isnull(sum(RUT_L*0.6*0.4),0) as RUT_L,isnull(sum(RUT_H*1*0.4),0) as RUT_H,isnull(sum([BLED]*0.2),0) as bled,
                            isnull(sum([REPAIR]*0.1),0) as repair from S_PMDS_ASP 
                            where UNIT in(select UNIT from B_INVNTORY_UNIT where SEGID='" + segid + "' "
                            +"and INSPECID in (select top 1 INSPECID from S_INSPEACCSE where INSPECID in"
                            + "(select distinct INSPECID from S_PMDS_ASP where UNIT in(select UNIT from B_INVNTORY_UNIT where SEGID='" + segid + "')) order by YEAR_M desc))";



            DataTable table = new DataTable();
            using (SqlConnection sqlCon = new SqlConnection(Adapter.ConnectionString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(sql, sqlCon))
                {
                    
                    da.Fill(table);
                    da.FillSchema(table, SchemaType.Source);
                }
            }
            return table;
        }
    }
}
